﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BEELab.Data;
using System.Data;
using System.Data.SqlClient;

namespace BEELab.Logic.ProgramManagement.SessionManagement
{
    public static class InvitationManagement
    {
        /// <summary>
        /// Creates new invitation to a specific session of an existing experiment
        /// </summary>
        /// <param name="SessionID">Session ID number</param>
        /// <param name="Name">Session new name</param>
        /// <returns>True id succeeded, False otherwise</returns>
        public static Boolean CreateInvitation(int SessionID, string Name)
        {
            //Creates new invitation and insert new invitation to DB
            SqlCommand command = new SqlCommand();
            command.CommandText = "INSERT INTO Invitations (SessionID, Name, Code) VALUES (@SessionID, @Name, @Code)";
            command.Parameters.Add(new SqlParameter("@SessionID", SessionID));
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Code", CryptographyHelper.GenerateRandomLongInt(15)));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        /// <summary>
        /// Deletes an existion invitation of a specific experiment session from DB
        /// </summary>
        /// <param name="SessionID">Session ID number</param>
        /// <param name="Name">Session new name</param>
        /// <returns>True if succeeded, False otherwise</returns>
        public static Boolean DeleteInvitation(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "DELETE FROM InitialValues WHERE ParentID=@ParentID AND Type='Invitation'";
            command.Parameters.Add(new SqlParameter("@ParentID", ID));

            DBHelper.ExecuteNonQuery(command);

            command = new SqlCommand();
            command.CommandText = "DELETE FROM Invitations WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res > 0)
                return true;

            return false;
        }

        /// <summary>
        /// Updates an existion invitation name on DB
        /// </summary>
        /// <param name="ID">Invitation ID number</param>
        /// <param name="Name">Invitation's new name</param>
        /// <returns>True if succeeded, False otherwise</returns>
        public static Boolean UpdateInvitation(int ID, string Name)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE Invitations SET Name=@Name WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        /// <summary>
        /// Updates one variable value in a specific experiment invitation. 
        /// If variabe doesn't exists on this invitation, will add it to invitation.
        /// </summary>
        /// <param name="InvitationID">Invitation ID</param>
        /// <param name="VariableID">Variable ID</param>
        /// <param name="Value">New Variable value</param>
        /// <returns>True if succeeded, False otherwise</returns>
        public static Boolean SetVariableValue(int InvitationID, int VariableID, string Value)
        {
            SqlCommand command = new SqlCommand();
            int res;

            //If VariableID is 0, update FirstStage on invitation
            if (VariableID == 0)
            {
                command = new SqlCommand();
                command.CommandText = "UPDATE Invitations SET FirstStage=@Value WHERE ID=@InvitationID";
                command.Parameters.Add(new SqlParameter("@Value", Value));
                command.Parameters.Add(new SqlParameter("@InvitationID", InvitationID));
                res = DBHelper.ExecuteNonQuery(command);

                if (res > 0)
                    return true;

                return false;
            }

            //else, if it's not firstStage- update other existing variable according to its variableID
            command = new SqlCommand();
            command.CommandText = "UPDATE InitialValues SET Value=@Value WHERE ParentID=@ParentID AND VariableID=@VariableID AND Type='Invitation'";
            command.Parameters.Add(new SqlParameter("@Value", Value));
            command.Parameters.Add(new SqlParameter("@ParentID", InvitationID));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));

            res = DBHelper.ExecuteNonQuery(command);

            if (res > 0)
                return true;

            //else, if it's not an existing variable, add it as new variable
            command = new SqlCommand();
            command.CommandText = "INSERT INTO InitialValues (ParentID, VariableID, Value, Type) VALUES (@ParentID, @VariableID, @Value, @Type)";
            command.Parameters.Add(new SqlParameter("@Value", Value));
            command.Parameters.Add(new SqlParameter("@ParentID", InvitationID));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));
            command.Parameters.Add(new SqlParameter("@Type", "Invitation"));

            res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        /// <summary>
        /// Gets invitationID and variable Name,
        /// and returns the value of the variable in the specific invitation
        /// </summary>
        /// <param name="InvitationID">Invitation ID</param>
        /// <param name="VariableName">Variable Name</param>
        /// <returns>The value of VariableName</returns>
        public static string GetVariableValue(int InvitationID, string VariableName)
        {
            //Retrieves relevant session from DB according to invitation ID
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT SessionID FROM Invitations WHERE ID=@InvitationID";
            command.Parameters.Add(new SqlParameter("@InvitationID", InvitationID));
            int SessionID = int.Parse(DBHelper.ExecuteScalar(command));

            //Retrieves relevant ExperimentID from DB according to Session ID from previous query
            //Uses to retrieve variableID by it's name from DB
            command = new SqlCommand();
            command.CommandText = "SELECT ProgramID FROM Sessions WHERE ID=@SessionID";
            command.Parameters.Add(new SqlParameter("@SessionID", SessionID));
            int ProgramID = int.Parse(DBHelper.ExecuteScalar(command));

            //Gets variable value according to invitationID and variableID
            return GetVariableValue(InvitationID, BEELab.Logic.ProgramManagement.VariableManagement.VariableManagement.GetIDByName(ProgramID, VariableName));
        }

        /// <summary>
        /// Gets invitationID and variableID,
        /// and returns the value of the variable in the specific invitation
        /// </summary>
        /// <param name="InvitationID">Invitation ID</param>
        /// <param name="VariableID">Variable ID</param>
        /// <returns>Variable value in the specific invitation (if changed), or in session if remain the same</returns>
        public static string GetVariableValue(int InvitationID, int VariableID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Value FROM InitialValues WHERE ParentID=@ParentID AND VariableID=@VariableID AND Type='Invitation'";
            command.Parameters.Add(new SqlParameter("@ParentID", InvitationID));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));

            string res = DBHelper.ExecuteScalar(command);

            if (res == null)
                return SessionManagement.GetVariableValue(GetSessionIDByInvitationID(InvitationID), VariableID);

            return res;
        }

        /// <summary>
        /// Gets invitation ID number and returns the relevant session ID number this invitation belongs to.
        /// </summary>
        /// <param name="InvitationID">Invitation ID</param>
        /// <returns>Session ID</returns>
        public static int GetSessionIDByInvitationID(int InvitationID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT SessionID FROM Invitations WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", InvitationID));
            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        /// <summary>
        /// Retrieves invitation code from DB
        /// </summary>
        /// <param name="InvitationID">Invitation ID</param>
        /// <returns>Invitation code</returns>
        public static string GetCode(int InvitationID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Code FROM Invitations WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", InvitationID));
            return DBHelper.ExecuteScalar(command);
        }

        /// <summary>
        /// Gets invitation code, and retrieves invitationIF drom DB
        /// </summary>
        /// <param name="Code">Invitation's Code</param>
        /// <returns>Invitation ID if succeeded, 0 otherwise.</returns>
        public static int GetInvitationIDByCode(string Code)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM Invitations WHERE Code=@Code";
            command.Parameters.Add(new SqlParameter("@Code", Code));
            string res = DBHelper.ExecuteScalar(command);

            if (res == null)
                return 0;

            return int.Parse(res);
        }

        /// <summary>
        /// Gets invitationID and returns invitation's Name from DB
        /// </summary>
        /// <param name="InvitationID">InvitationID</param>
        /// <returns>Invitation name</returns>
        public static string GetName(int InvitationID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Name FROM Invitations WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", InvitationID));
            return DBHelper.ExecuteScalar(command);
        }

        /// <summary>
        /// Returns a list of all existing invitations to a specific session of an experiment.
        /// </summary>
        /// <param name="ID">Session ID number</param>
        /// <returns>List of all existing invitations for this session</returns>
        public static List<int> ListBySessionID(int ID)
        {
            List<int> result = new List<int>();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM Invitations WHERE SessionID=@SessionID";
            command.Parameters.Add(new SqlParameter("@SessionID", ID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            foreach (DataRow dr in dt.Rows)
            {
                result.Add((int)dr["ID"]);
            }

            return result;
        }

        /// <summary>
        /// Gets invitationID and returns invitation first stage (if changed), or session first stage.
        /// </summary>
        /// <param name="InvitationID">Invitation ID</param>
        /// <returns>First stage of this invitation (if changed), or of session (if remain the same). </returns>
        public static string GetFirstStage(int InvitationID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT FirstStage FROM Invitations WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", InvitationID));
            string res = DBHelper.ExecuteScalar(command);

            if (res != null && res.Length > 0)
                return res;

            return SessionManagement.GetFirstStage(GetSessionIDByInvitationID(InvitationID));
        }

        /// <summary>
        /// Gets invitation ID and returns the owner ID of the user who creates this experiment originally.
        /// </summary>
        /// <param name="ID">invitation ID</param>
        /// <returns>Owner ID of the user who creates this experiment</returns>
        public static int GetOwnerID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Programs.OwnerID FROM Invitations JOIN Sessions ON Invitations.SessionID=Sessions.ID JOIN Programs ON Programs.ID=Sessions.ProgramID WHERE Invitations.ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            string res = DBHelper.ExecuteScalar(command);

            if (res != null && res.Length > 0)
            {
                return int.Parse(res);
            }

            return 0;
        }
    }

}